package repositories;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;
import java.util.Vector;

import domain.*;

public class PaperRepository extends BaseRepository {

	public void createPaper(Paper entity) throws Exception {
		if (entity == null)
			throw new Exception("Paper entity is null.");

		if (!hasPaper(entity.getDblpUrl())) {
			AuthorRepository authorRepository = new AuthorRepository();
			for (Author author : entity.getAuthors()) {
				authorRepository.saveAuthor(author);
				//System.out.println(author.getAuthorID());
			}

			this.savePaper(entity);
			this.saveCoauthors(entity);
			this.saveAuthors(entity);
		}
	}

	public boolean hasPaper(String dblpUrl) throws Exception {
		if (dblpUrl == null || dblpUrl.trim() == "")
			throw new Exception("DBLP Url may not be empty.");

		boolean result = false;

		String sql = String.format("SELECT " + "  COUNT(*) AS Count " + "FROM "
				+ "  papers " + "WHERE " + "  DblpUrl LIKE '%s';",
				super.cleanString(dblpUrl));

		Connection conn = null;

		try {
			conn = super.getConnection();
			ResultSet results = super.executeQuery(conn, sql);

			if (results.next())
				result = results.getInt("count") > 0;
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			super.closeConnection(conn);
		}

		return result;
	}

	public void updatePaperAuthorID(UUID authorID, UUID oldID) {
		String allAuthorsName = String
				.format("UPDATE paper_authors SET AuthorID=\""
						+ authorID.toString() + "\" " + "WHERE AuthorID=\""
						+ oldID.toString() + "\"");
		super.executeUpdate(allAuthorsName);
	}

	public void savePaper(Paper entity) throws Exception {
		if (entity == null)
			throw new Exception("Paper entity is null.");

		String sql = String.format("REPLACE INTO " + "  papers "
				+ "  (PaperID, Title, Year, DblpUrl, DocumentUrl, Created) "
				+ "VALUES " + "  ('%s', '%s', %d, '%s', '%s', NOW());",
				entity.getPaperID(), cleanString(entity.getTitle()),
				entity.getYear(), entity.getDblpUrl(), entity.getDocumentUrl());

		super.executeUpdate(sql);
	}

	public void saveAuthors(Paper entity) throws Exception {
		if (entity == null)
			throw new Exception("Paper entity is null.");

		if (entity.getAuthors().size() > 0) {
			Vector<String> values = new Vector<String>();
			for (Author author : entity.getAuthors()) {
				values.add(String.format("('%s','%s')", entity.getPaperID(),
						author.getAuthorID()));
			}

			String sql = "REPLACE INTO " + "  paper_authors "
					+ "  (PaperID, AuthorID) " + "VALUES "
					+ values.toString().replaceAll("[\\[\\]]", "");

			super.executeUpdate(sql);
		}
	}

	public void saveCoauthors(Paper entity) throws Exception {
		if (entity == null)
			throw new Exception("Paper entity is null.");

		if (entity.getAuthors().size() > 1) {

			Vector<String> values = new Vector<String>();
			for (Author author : entity.getAuthors()) {
				for (Author coauthor : entity.getAuthors()) {
					if (author.getAuthorID() != coauthor.getAuthorID())
						values.add(String.format("('%s','%s','%s','%s')",
								author.getAuthorID(), coauthor.getAuthorID(),
								super.cleanString(author.getFullName()),
								super.cleanString(coauthor.getFullName())));
				}
			}

			String sql = "REPLACE INTO "
					+ "  coauthors "
					+ "  (AuthorID, CoauthorID, AuthorName, CoAuthorName) "
					+ "VALUES " + values.toString().replaceAll("[\\[\\]]", "");

			super.executeUpdate(sql);
		}
	}
}
